//api.js

const connection = require('./db')
const fs = require('fs')
const tools = require('../tools')
//查询
const queryData = (param) => {
    return new Promise((resolve, reject) => {
        //第一个参数：sql语句
        //第二个参数：回调函数（err：查询错误，data：查询结果）
        let sql = "select * from " + param.type
        if (param.id)
            sql += " where id = " + param.id
        connection.query(sql, (err, data) => {
            if (err) console.log(err)
            resolve(data)
        })
    })
}
//添加
const insertData = (param, type) => {
    return new Promise((resolve, reject) => {
        let sql = ''
        if (type == 'character') {
            sql = "insert into characters (name, title, description, attributes, avatar, sprites) values(?,?,?,?,?,?)"
        } else {
            sql = "insert into " + type + " (name, description, attributes, icon) values(?,?,?,?)"
        }
        connection.query(sql, param, (err, data) => {
            //如果err为null则成功
            if (err) console.log(err)
            resolve(data)
        })
    })
}
//改
const updateData = (type, id, param) => {
    return new Promise((resolve, reject) => {
        connection.query("update " + type + " set name = ?,description=?, attributes=?, icon=? where id = " + id, param, (err, data) => {
            //如果err为null则成功
            if (err) console.log(err)
            resolve(data)
        })
    })
}
const updateCharacter = (type, id, param) => {
    return new Promise((resolve, reject) => {
        connection.query("update " + type + " set name = ?, title= ?, description= ?, attributes= ?, avatar= ?, sprites= ? where id = " + id, param, (err, data) => {
            //如果err为null则成功
            if (err) console.log(err)
            resolve(data)
        })
    })
}

//删除
const deleteData = (id, type) => {
    return new Promise((resolve, reject) => {
        // 先执行原始的删除操作
        connection.query(`DELETE FROM \`${type}\` WHERE id = ?`, id, (err, data) => {
            if (err) {
                console.error(err);
                reject(err);
            } else {
                // 根据删除的类型决定是否需要进一步删除characters_skills表中的记录
                if (type === 'characters' || type === 'skills') {
                    const relatedTable = type === 'characters' ? 'character_id' : 'skill_id';
                    // 构造并执行额外的删除语句
                    connection.query(`DELETE FROM character_skills WHERE ${relatedTable} = ?`, id, (err2, data2) => {
                        if (err2) {
                            console.error(err2);
                            // 如果第二步出错，可以选择回滚之前的操作以保持数据一致性，这里直接reject错误
                            reject(err2);
                        } else {
                            resolve({ [type]: data, 'characters_skills': data2 });
                        }
                    });
                } else {
                    resolve({ [type]: data });
                }
            }
        });
    });
};

const queryCharacterSkills = () => {
    return new Promise((resolve, reject) => {
        //第一个参数：sql语句
        //第二个参数：回调函数（err：查询错误，data：查询结果）
        let sql = `SELECT
        characters.id as character_id,
        characters.name as character_name,
        characters.title as character_title,
        characters.description as character_description,
        characters.attributes as character_attributes,
        characters.avatar as character_avatar,
        characters.sprites as character_sprites,
        GROUP_CONCAT(character_skills.id) AS ids,
        GROUP_CONCAT(skills.name) AS skill_names,
        GROUP_CONCAT(skills.id) AS skill_ids,
        GROUP_CONCAT(skills.icon) AS skill_icons
    FROM 
        characters
    LEFT JOIN character_skills ON characters.id = character_skills.character_id
    LEFT JOIN skills ON character_skills.skill_id = skills.id
    GROUP BY 
        characters.id,
        characters.name,
        characters.title,
        characters.description,
        characters.attributes,
        characters.avatar,
        characters.sprites
`
        connection.query(sql, (err, data) => {
            if (err) console.log(err)
            resolve(data)
        })
    })
}

const insertCharacterSkills = (param) => {
    return new Promise((resolve, reject) => {
        let sql = "insert into character_skills (character_id,skill_id) values(?,?)"
        connection.query(sql, param, (err, data) => {
            //如果err为null则成功
            if (err) console.log(err)
            resolve(data)
        })
    })
}

const deleteCharacterSkills = (id) => {
    return new Promise((resolve, reject) => {
        connection.query("delete from character_skills where id = ?", id, (err, data) => {
            if (err) console.log(err)
            resolve(data)
        })
    })
}
const exportDatas = () => {
    return new Promise((resolve, reject) => {
        let dir = tools.getDateFormat(new Date())
        fs.mkdirSync('./export/' + dir, { recursive: true })
        getCharacters(dir)
        getSkills(dir)
        buffers(dir)
        getItems(dir)
        getEquipments(dir)
        resolve("ok")
    })
}

const getCharacters = (dir) => {
    queryCharacterSkills().then(data => {
        var characters = []
        for (const iterator of data) {
            characters.push({
                id: iterator.character_id,
                name: iterator.character_name,
                title: iterator.character_title,
                description: iterator.character_description,
                attributes: iterator.character_attributes,
                avatar: iterator.character_avatar,
                sprites: iterator.character_sprites,
                skill_ids: iterator.skill_ids
            })
        }
        fs.writeFileSync('./export/' + dir + '/characters.csv', JSON.stringify(characters))
        console.log('导出characters成功')
    })
}

const getSkills = (dir) => {
    queryData({ type: 'skills' }).then(data => {
        var skills = []
        for (const iterator of data) {
            skills.push({
                id: iterator.id,
                name: iterator.name,
                description: iterator.description,
                attributes: iterator.attributes,
                icon: iterator.icon
            })
        }
        fs.writeFileSync('./export/' + dir + '/skills.csv', JSON.stringify(skills))
        console.log('导出skills成功')
    })
}

const buffers = (dir) => {
    queryData({ type: 'buffers' }).then(data => {
        var buffers = []
        for (const iterator of data) {
            buffers.push({
                id: iterator.id,
                name: iterator.name,
                description: iterator.description,
                attributes: iterator.attributes,
                icon: iterator.icon
            })
        }
        fs.writeFileSync('./export/' + dir + '/buffers.csv', JSON.stringify(buffers))
        console.log('导出buffers成功')
    })
}

const getItems = (dir) => {
    queryData({ type: 'items' }).then(data => {
        var buffers = []
        for (const iterator of data) {
            buffers.push({
                id: iterator.id,
                name: iterator.name,
                description: iterator.description,
                attributes: iterator.attributes,
                icon: iterator.icon
            })
        }
        fs.writeFileSync('./export/' + dir + '/items.csv', JSON.stringify(buffers))
        console.log('导出items成功')
    })
}
const getEquipments = (dir) => {
    queryData({ type: 'equipments' }).then(data => {
        var buffers = []
        for (const iterator of data) {
            buffers.push({
                id: iterator.id,
                name: iterator.name,
                description: iterator.description,
                attributes: iterator.attributes,
                icon: iterator.icon
            })
        }
        fs.writeFileSync('./export/' + dir + '/equipments.csv', JSON.stringify(buffers))
        console.log('导出items成功')
    })
}

//导出方法，在需要使用到的模块中导入
module.exports = {
    queryData,
    insertData,
    updateData,
    updateCharacter,
    deleteData,
    queryCharacterSkills,
    insertCharacterSkills,
    deleteCharacterSkills,
    exportDatas
}